﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Data;
using System.Data.Common;
using System.Transactions;
using System.Configuration;

/// <summary>
/// db 的摘要描述
/// </summary>
public class DB
{
    public DB() : this("ConnStr")
    {
        //
        // TODO: 在此加入建構函式的程式碼
        //

        
    }

    public DB(string activeConnName)
    {
        this.activeConnName = activeConnName;
        factory = DbProviderFactories.GetFactory(ActiveConnSetting.ProviderName);
    }

    private DbProviderFactory factory = null;
    private static Dictionary<Transaction, Dictionary<string, DbConnection>> trans = new Dictionary<Transaction, Dictionary<string, DbConnection>>();

    private string activeConnName = null;

    private ConnectionStringSettings ActiveConnSetting
    {
        get
        {
            if (string.IsNullOrEmpty(activeConnName))
            {
                return WebConfigurationManager.ConnectionStrings["connstr"];
            }
            else
            {
                return WebConfigurationManager.ConnectionStrings[activeConnName];
            }
        }
    }

    public DataTable Execute(string query, params DBParameter[] paras)
    {
        var conn = GetConn();
        var cmd = factory.CreateCommand();
        var da = factory.CreateDataAdapter();

        cmd.Connection = conn;
        cmd.CommandText = query;
        //MyObj.WriteLog(paras.Length.ToString());
        foreach (DBParameter p in paras)
        {
            var para = factory.CreateParameter();

            para.ParameterName = p.Name;
            para.Value = p.Value;

            cmd.Parameters.Add(para);
        }

        da.SelectCommand = cmd;

        var ds = new DataSet();

        da.Fill(ds);
        //MyObj.WriteLog(conn.State.ToString());
        CloseConn(conn);

        return ds.Tables[0];
    }

    public int ExecuteNonQuery(string query, List<DBParameter> paras)
    {
        return ExecuteNonQuery(query, paras.ToArray());
    }

    public int ExecuteNonQuery(string query, params DBParameter[] paras)
    {
        var conn = GetConn();
        var cmd = factory.CreateCommand();

        cmd.Connection = conn;
        cmd.CommandText = query;
        //MyObj.WriteLog(paras.Length.ToString());
        foreach (DBParameter p in paras)
        {
            var para = factory.CreateParameter();

            para.ParameterName = p.Name;
            para.Value = p.Value;

            cmd.Parameters.Add(para);
        }

        var result = cmd.ExecuteNonQuery();

        CloseConn(conn);

        return result;
    }

    public List<T> ExecuteListScalar<T>(string query, params DBParameter[] paras)
    {
        var dt = Execute(query, paras);
        var list = new List<T>();

        foreach(DataRow dr in dt.Rows)
        {
            list.Add((T)Convert.ChangeType(dr[0], typeof(T)));
        }

        return list;
    }

    public T ExecuteScalar<T>(string query, params DBParameter[] paras)
    {
        var dt = Execute(query, paras);

        if (dt.Rows.Count == 0)
        {
            return default(T);
        }
        else
        {
            if (dt.Rows[0][0] == DBNull.Value)
            {
                return default(T);
            }
            else
            {
                return (T)Convert.ChangeType(dt.Rows[0][0], typeof(T));
            }
        }
    }

    public DataRow ExecuteRow(string query, params DBParameter[] paras)
    {
        var dt = Execute(query, paras);

        if (dt.Rows.Count == 0)
        {
            return null;
        }
        else
        {
            return dt.Rows[0];
        }
    }

    public DbConnection GetConn()
    {
        //Dictionary<string, DbConnection> conns = null;

        if (Transaction.Current == null)
        {
            return CreateConn();
        }
        else
        {
            if (trans.ContainsKey(Transaction.Current))
            {
                if (trans[Transaction.Current].ContainsKey(ActiveConnSetting.ConnectionString))
                {
                    return trans[Transaction.Current][ActiveConnSetting.ConnectionString];
                }
            }
            else
            {
                Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(Current_TransactionCompleted);

                trans.Add(Transaction.Current, new Dictionary<string, DbConnection>());
            }
        }
        
        var conns = trans[Transaction.Current];

        var conn = CreateConn();

        conns.Add(ActiveConnSetting.ConnectionString, conn);

        return conn;
    }

    void Current_TransactionCompleted(object sender, TransactionEventArgs e)
    {
        foreach(var conn in trans[sender as Transaction].Values)
        {
            conn.Close();
        }

        trans.Remove(sender as Transaction);
    }

    public DbConnection CreateConn()
    {
        var conn = factory.CreateConnection();

        conn.ConnectionString = ActiveConnSetting.ConnectionString;
        conn.Open();

        return conn;
    }

    public void CloseConn(DbConnection conn)
    {
        if (Transaction.Current == null)
        {
            conn.Close();
        }
    }

    public int GetIdentity()
    {
        return ExecuteScalar<int>("SELECT @@IDENTITY ");
    }
}